package com.cn.hotel.dao.Impl;

import com.cn.hotel.dao.INewsDao;
import com.cn.hotel.model.NewsModel;
import com.cn.hotel.model.NewsTypeModel;
import com.cn.hotel.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsImpl implements INewsDao {
    /**
     * 获取数据库新闻总条数
     *
     * @param newsModel
     * @return
     */
    @Override
    public int getCount(NewsModel newsModel) {
        StringBuilder sql = new StringBuilder("select count(*) count from news where 1=1");
        if (newsModel.getNewsType() != null && !"".equals(newsModel.getNewsType())) {
            sql.append(" and news_type like '%" + newsModel.getNewsType() + "%'");
        }
        if (newsModel.getNewsTitle() != null && !"".equals(newsModel.getNewsTitle())) {
            sql.append(" and news_title like '%" + newsModel.getNewsTitle() + "%'");
        }
        if (newsModel.getPublisher() != null && !"".equals(newsModel.getPublisher())) {
            sql.append(" and publisher like '%" + newsModel.getPublisher() + "%'");
        }
        if (!"".equals(newsModel.getStartReleaseTime())&&"".equals(newsModel.getEndReleaseTime())){
            sql.append(" and release_time > " + newsModel.getStartReleaseTime());
        }
        if ("".equals(newsModel.getStartReleaseTime())&&!"".equals(newsModel.getEndReleaseTime())){
            sql.append(" and release_time < " + newsModel.getEndReleaseTime());
        }
        if (!"".equals(newsModel.getStartReleaseTime())&&!"".equals(newsModel.getEndReleaseTime())){
            sql.append(" and release_time between '"+newsModel.getStartReleaseTime()+"' and '"+newsModel.getEndReleaseTime()+"'");
        }
        sql.append(" order by id asc limit " + (newsModel.getPageModel().getPageNum() - 1) * newsModel.getPageModel().getPageSize() + "" +
                "," + newsModel.getPageModel().getPageSize() + "");
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        int count = 0;
        try {
            resultSet.next();
            count = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 查询所有新闻
     *
     * @param newsModel
     * @return
     */
    @Override
    public List<NewsModel> findAll(NewsModel newsModel) {
        StringBuilder sql = new StringBuilder("select * from news where 1=1");
        if (newsModel.getNewsType() != null && !"".equals(newsModel.getNewsType())) {
            sql.append(" and news_type like '%" + newsModel.getNewsType() + "%'");
        }
        if (newsModel.getNewsTitle() != null && !"".equals(newsModel.getNewsTitle())) {
            sql.append(" and news_title like '%" + newsModel.getNewsTitle() + "%'");
        }
        if (newsModel.getPublisher() != null && !"".equals(newsModel.getPublisher())) {
            sql.append(" and publisher like '%" + newsModel.getPublisher() + "%'");
        }
        if (!"".equals(newsModel.getStartReleaseTime())&&"".equals(newsModel.getEndReleaseTime())){
            sql.append(" and release_time > " + newsModel.getStartReleaseTime());
        }
        if ("".equals(newsModel.getStartReleaseTime())&&!"".equals(newsModel.getEndReleaseTime())){
            sql.append(" and release_time < " + newsModel.getEndReleaseTime());
        }
        if (!"".equals(newsModel.getStartReleaseTime())&&!"".equals(newsModel.getEndReleaseTime())){
            sql.append(" and release_time between '"+newsModel.getStartReleaseTime()+"' and '"+newsModel.getEndReleaseTime()+"'");
        }
        sql.append(" order by id asc limit " + (newsModel.getPageModel().getPageNum() - 1) * newsModel.getPageModel().getPageSize() + "" +
                "," + newsModel.getPageModel().getPageSize() + "");
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        List<NewsModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                model.setId(resultSet.getInt("id"));
                model.setNewsType(resultSet.getString("news_type"));
                model.setNewsTitle(resultSet.getString("news_title"));
                model.setPublisher(resultSet.getString("publisher"));
                model.setNewsContent(resultSet.getString("news_content"));
                model.setReleaseTime(resultSet.getString("release_time"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 添加新闻
     *
     * @param newsModel
     * @return
     */
    @Override
    public int add(NewsModel newsModel) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into news(news_type,news_title,publisher,news_content,release_time)  " +
                " values('" + newsModel.getNewsType() + "','" + newsModel.getNewsTitle() + "" +
                "','" + newsModel.getPublisher() + "','" + newsModel.getNewsContent() + "','" + newsModel.getReleaseTime() + "')");
        return JDBC.executeUpdate(sql.toString());
    }

    /**
     * 通过id查询
     *
     * @param temp
     * @return
     */
    @Override
    public NewsModel findById(Integer temp) {
        StringBuilder sql = new StringBuilder();
        if (temp != 0) {
            sql.append("select * from news where id=" + temp);
        }
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        NewsModel model = new NewsModel();
        try {
            while (resultSet.next()) {
                model.setId(resultSet.getInt("id"));
                model.setNewsType(resultSet.getString("news_type"));
                model.setNewsTitle(resultSet.getString("news_title"));
                model.setPublisher(resultSet.getString("publisher"));
                model.setNewsContent(resultSet.getString("news_content"));
                model.setReleaseTime(resultSet.getString("release_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    /**
     * 保存新闻修改
     *
     * @param newsModel
     * @return
     */
    @Override
    public int newsEdit(NewsModel newsModel) {
        StringBuilder sql = new StringBuilder();
        sql.append("update news set ");
        if (newsModel.getNewsType() != null && !"".equals(newsModel.getNewsType())) {
            sql.append(" news_type ='" + newsModel.getNewsType() + "',");
        }
        if (newsModel.getNewsTitle() != null && !"".equals(newsModel.getNewsTitle())) {
            sql.append(" news_title ='" + newsModel.getNewsTitle() + "',");
        }
        if (newsModel.getPublisher() != null && !"".equals(newsModel.getPublisher())) {
            sql.append(" publisher = '" + newsModel.getPublisher() + "',");
        }
        if (newsModel.getNewsContent() != null && !"".equals(newsModel.getNewsContent())) {
            sql.append(" news_content='" + newsModel.getNewsContent() + "',");
        }
        if (newsModel.getReleaseTime() != null && !"".equals(newsModel.getReleaseTime())) {
            sql.append(" release_time='" + newsModel.getReleaseTime() + "',");
        }
        sql.append(" remark='' where id ="+newsModel.getId()+"");
        return JDBC.executeUpdate(sql.toString());
    }

    /**
     * 删除
     * @param aa
     * @return
     */
    @Override
    public int delete(Integer aa) {
        StringBuilder sql = new StringBuilder();
        sql.append("delete from news where id="+aa);
        int count = JDBC.executeUpdate(sql.toString());
        return count;
    }

    /**
     * 新闻类型获取
     * @return
     */
    @Override
    public List<NewsTypeModel> getNewsTypeModelList() {
        StringBuilder sql = new StringBuilder("select * from  news_type");
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        List<NewsTypeModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsTypeModel model = new NewsTypeModel();
                model.setNewsType(resultSet.getString("type_name"));
                model.setNewHref("news_href");
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 网站首页调取五条最新新闻数据
     * @return
     */
    @Override
    public List<NewsModel> getHomeNewsModelList() {
        StringBuilder sql = new StringBuilder("select * from news order by id desc limit 5");
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        List<NewsModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                model.setNewsTitle(resultSet.getString("news_title"));
                model.setReleaseTime(resultSet.getString("release_time"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 新闻列表获取
     * @return
     */
    @Override
    public List<NewsModel> getNewsModelList() {
        StringBuilder sql = new StringBuilder("select * from  news order by id desc limit 10 ");
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        List<NewsModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                model.setNewsType(resultSet.getString("news_type"));
                model.setNewsTitle(resultSet.getString("news_title"));
                model.setReleaseTime(resultSet.getString("release_time"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
